package com.datamining.service;

import com.datamining.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;

public class AlgorithService {

	/**
	 * 统计该数据表的字段名
	 * @param table_name 数据表名
	 * @return 此数据集的所有字段名（开头是id，最后是结果名）
	 */
	public List<String> findFieldName(String table_name) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<String> fieldList = new ArrayList<>();
		// 获得连接
		try {
			conn = JdbcUtil.getConnection();
			stat = conn.prepareStatement("select COLUMN_NAME from information_schema.columns where table_name= ? ");	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入
			stat.setString(1, table_name);

			// 执行语句
			rs = stat.executeQuery();
//			rs.next();//跳过id
			while(rs.next()){
				fieldList.add(rs.getString("COLUMN_NAME"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
			JdbcUtil.close(rs);
			return fieldList;
		}
	}

	/**
	 * 找出该字段有哪些属性
	 * @param table_name 数据表名
	 * @param field_name 字段名
	 * @return 该字段包含的属性
	 */
	public List<String> findField(String table_name , String field_name) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<String> distinctField = new ArrayList<>();
		StringBuffer sql = new StringBuffer("SELECT DISTINCT");
		// 获得连接
		try {
			conn = JdbcUtil.getConnection();
			sql.append(" " + field_name + " FROM");
			sql.append(" " + table_name + ";");
			stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入
			// 执行语句
			rs = stat.executeQuery();
			while(rs.next()){
				distinctField.add(rs.getString(field_name));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
			JdbcUtil.close(rs);
			return distinctField;
		}
	}

	/**
	 *统计该字段的某属性在该数据表中出现几次
	 * @param table_name 数据表名
	 * @param field_name 字段名
	 * @return 用map保存的该属性的出现次数
	 */
	public Map<String,Integer> findFieldPropertyCount(String table_name , String field_name) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		Map<String,Integer> propertyCount = new HashMap<>();
		StringBuffer sql = new StringBuffer("SELECT");
		// 获得连接
		try {
			conn = JdbcUtil.getConnection();
			sql.append(" " + field_name + ",COUNT(" + field_name + ")" + " FROM");
			sql.append(" " + table_name + " " + "GROUP BY " + field_name + ";");
			stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入
			// 执行语句
			rs = stat.executeQuery();
			while(rs.next()){
				propertyCount.put(rs.getString(field_name) , rs.getInt("COUNT(" + field_name + ")"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
			JdbcUtil.close(rs);
			return propertyCount;
		}
	}

	/**
	 * 遍历id3算法的条件集
	 * @param table_name 数据表名
	 * @return 全部条件集
	 */
	public List<List<String>> fieldAllCount(String table_name) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<List<String>> countList = new ArrayList<List<String>>();
//		List<String> fieldList = this.findFieldName(table_name);
		List<String> Count = this.findFieldName(table_name);//单纯用来计数
		// 获得连接
		try {
			conn = JdbcUtil.getConnection();
			StringBuffer sql = new StringBuffer("select * from ");
			sql.append(" " + table_name);
			stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

			// 执行语句
			rs = stat.executeQuery();

			while(rs.next()){
//				fieldList.add(rs.getString("COLUMN_NAME"));
				List<String> fieldList = new ArrayList<String>();
				for (int i = 0; i < Count.size(); i++) {
					fieldList.add(rs.getString(Count.get(i)).toString());
				}
				countList.add(fieldList);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn);
			JdbcUtil.close(stat);
			JdbcUtil.close(rs);
			return countList;
		}

	}


    /**
     * 根据id找出字段集的个数
     * @param table_name 数据表名
     * @param result_name 字段名
     * @param idList id集
     * @return 字段集每个字段的个数
     */
	public Map<String,Integer> findResultMap(String table_name ,String result_name , List<Integer> idList) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<String> countData = new ArrayList<>();
        List<String> fieldName = this.findField(table_name , result_name);
        Map<String,Integer> resultMap = new HashMap<>();

        // 获得连接

        for (int i = 0; i < idList.size(); i++) {
            try {
                conn = JdbcUtil.getConnection();
                StringBuffer sql = new StringBuffer("select * from ");
                sql.append(" " + table_name);
                sql.append(" " + "where id = ?");
                stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

                stat.setInt(1 , idList.get(i));

                // 执行语句
                rs = stat.executeQuery();

                while (rs.next()) {
                    countData.add(rs.getString(result_name));//易错点
                }

            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn);
                JdbcUtil.close(stat);
                JdbcUtil.close(rs);

            }
        }

        for (int i = 0; i < fieldName.size(); i++) {
            resultMap.put(fieldName.get(i) , Collections.frequency(countData , fieldName.get(i)));
//            resultCount.add(Collections.frequency(countData , fieldName.get(i)));
        }

        return resultMap;
    }


    /**
     * 找出该字段各属性的id集
     * @param table_name 数据表名
     * @param field_name 字段名
     * @return 该字段各属性的id集
     */
    public Map<String , List<Integer>> findIdList(String table_name , String field_name) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<String> fieldName = this.findField(table_name , field_name);
        Map<String , List<Integer>> idMap = new HashMap<>();


        for (int i = 0; i < fieldName.size(); i++) {
            List<Integer> idList = new ArrayList<>();
            // 获得连接
            try {

                conn = JdbcUtil.getConnection();
                StringBuffer sql = new StringBuffer("select * from ");
                sql.append(" " + table_name);
                sql.append(" " + "where" + " " + field_name + "=?");
                stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

                stat.setString(1 , fieldName.get(i));
                // 执行语句
                rs = stat.executeQuery();

                while(rs.next()){
//				fieldList.add(rs.getString("COLUMN_NAME"));
                    idList.add(rs.getInt("id"));

                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn);
                JdbcUtil.close(stat);
                JdbcUtil.close(rs);
            }
            idMap.put(fieldName.get(i),idList);
        }
	    return idMap;
    }

    /**
     * 根据id集区分结果集
     * @param table_name 数据表名
     * @param idList id集
     * @return
     */
    public Map<String,List<Integer>> divIdList(String table_name,List<Integer> idList) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<String> fieldName = this.findFieldName(table_name);
        String result = fieldName.get(fieldName.size()-1);
        List<String> resultList = this.findField(table_name,result);
        Map<String , List<Integer>> idMap = new HashMap<>();


        for (int i = 0; i < resultList.size(); i++) {
            List<Integer> idList1 = new ArrayList<>();
            // 获得连接
            try {

                conn = JdbcUtil.getConnection();
                StringBuffer sql = new StringBuffer("select * from ");
                sql.append(" " + table_name);
                sql.append(" " + "where" + " " + result + "=?");
                stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

                stat.setString(1 , resultList.get(i));
                // 执行语句
                rs = stat.executeQuery();

                while(rs.next()){
//				fieldList.add(rs.getString("COLUMN_NAME"));
                    idList1.add(rs.getInt("id"));
                }
                idList1.retainAll(idList);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn);
                JdbcUtil.close(stat);
                JdbcUtil.close(rs);
            }
            idMap.put(resultList.get(i),idList1);
        }
        return idMap;
	}

    /**
     * 在现有id集里根据属性区分结果集
     * @param table_name
     * @param idList
     * @param field_name
     * @param property_name
     * @return
     */
    public Map<String,List<Integer>> findById(String table_name,List<Integer> idList,String field_name,String property_name) {
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        List<String> countData = new ArrayList<>();
        List<String> fieldName = this.findFieldName(table_name);
        String result = fieldName.get(fieldName.size()-1);
        List<String> resultList = this.findField(table_name,result);

        Map<String , List<Integer>> idMap = new HashMap<>();

        for (int i = 0; i < resultList.size(); i++) {
            List<Integer> idList1 = new ArrayList<>();
            // 获得连接
            try {

                conn = JdbcUtil.getConnection();
                StringBuffer sql = new StringBuffer("select * from ");
                sql.append(" " + table_name);
                sql.append(" " + "where" + " " + field_name + "=?");
                sql.append(" " + "and" + " " + result + "=?");
                stat = conn.prepareStatement(sql.toString());	// PreparedStatement 可以缓存SQL语句，可以参数化sql语句。提高性能，防止注入

                stat.setString(1,property_name);
                stat.setString(2,resultList.get(i));
                // 执行语句
                rs = stat.executeQuery();

                while(rs.next()){
//				fieldList.add(rs.getString("COLUMN_NAME"));
                    idList1.add(rs.getInt("id"));
                }
                idList1.retainAll(idList);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                JdbcUtil.close(conn);
                JdbcUtil.close(stat);
                JdbcUtil.close(rs);
            }
            idMap.put(resultList.get(i),idList1);
        }
        return idMap;
    }

}
